package com.example.pt.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.pt.entity.TorrentClickHistory;
import com.example.pt.entity.TorrentFile;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Insert;

import java.util.Date;
import java.util.List;
import java.util.Map;

@Mapper
public interface TorrentClickHistoryMapper extends BaseMapper<TorrentClickHistory> {
    
    /**
     * 获取用户最近点击的种子历史
     */
    @Select("SELECT * FROM torrent_click_history WHERE uid = #{uid} ORDER BY click_time DESC LIMIT #{limit}")
    List<TorrentClickHistory> findRecentClicksByUser(@Param("uid") Integer uid, @Param("limit") Integer limit);
    
    /**
     * 获取指定时间范围内的热门种子
     */
    @Select("SELECT tid, COUNT(*) as click_count FROM torrent_click_history " +
           "WHERE click_time BETWEEN #{startTime} AND #{endTime} " +
           "GROUP BY tid ORDER BY click_count DESC LIMIT #{limit}")
    List<Map<String, Object>> findHotTorrentsInPeriod(@Param("startTime") Date startTime, 
                                                     @Param("endTime") Date endTime, 
                                                     @Param("limit") Integer limit);
    
    /**
     * 获取用户点击的种子类别统计
     */
    @Select("SELECT t.category, COUNT(*) as click_count " +
           "FROM torrent_click_history h " +
           "JOIN torrentfile t ON h.tid = t.tid " +
           "WHERE h.uid = #{uid} " +
           "GROUP BY t.category")
    List<Map<String, Object>> getUserCategoryClickStats(@Param("uid") Integer uid);
    
    /**
     * 获取用户点击的种子总数
     */
    @Select("SELECT COUNT(*) FROM torrent_click_history WHERE uid = #{uid}")
    int getUserTotalClicks(@Param("uid") Integer uid);
    
    /**
     * 获取系统中最高浏览量
     */
    @Select("SELECT MAX(view_count) FROM torrentfile WHERE status = 1")
    int getMaxViewCount();
    
    /**
     * 直接执行SQL插入点击历史记录（忽略外键约束）
     */
    @Insert("INSERT IGNORE INTO torrent_click_history (uid, tid, click_time) VALUES (#{uid}, #{tid}, #{clickTime})")
    int insertIgnoreFK(@Param("uid") Integer uid, @Param("tid") Integer tid, @Param("clickTime") Date clickTime);
    
    /**
     * 检查用户是否存在
     */
    @Select("SELECT COUNT(*) FROM user WHERE uid = #{uid}")
    int checkUserExists(@Param("uid") Integer uid);
    
    /**
     * 检查种子是否存在
     */
    @Select("SELECT COUNT(*) FROM torrentfile WHERE tid = #{tid}")
    int checkTorrentExists(@Param("tid") Integer tid);
    
    /**
     * 根据ID列表获取种子信息
     */
    @Select("<script>" +
            "SELECT * FROM torrentfile WHERE tid IN " +
            "<foreach item='item' collection='ids' open='(' separator=',' close=')'>" +
            "#{item}" +
            "</foreach>" +
            "</script>")
    List<TorrentFile> findTorrentsByIds(@Param("ids") List<Integer> ids);
    
    /**
     * 查找与指定用户相似的用户
     * 相似度基于共同点击的种子数量
     */
    @Select("SELECT u2.uid, " +
            "COUNT(*) as common_clicks, " +
            "COUNT(*) / (SELECT COUNT(*) FROM torrent_click_history WHERE uid = #{uid}) as similarity " +
            "FROM torrent_click_history u1 " +
            "JOIN torrent_click_history u2 ON u1.tid = u2.tid AND u1.uid != u2.uid " +
            "WHERE u1.uid = #{uid} " +
            "GROUP BY u2.uid " +
            "HAVING COUNT(*) > 2 " + // 至少有3个共同点击
            "ORDER BY similarity DESC " +
            "LIMIT 10")
    List<Map<String, Object>> findSimilarUsers(@Param("uid") Integer uid);
    
    /**
     * 获取指定用户对指定种子的点击次数
     */
    @Select("SELECT COUNT(*) FROM torrent_click_history WHERE uid = #{uid} AND tid = #{tid}")
    int getUserTorrentClickCount(@Param("uid") int uid, @Param("tid") Integer tid);
    
    /**
     * 获取指定用户对指定种子的点击时间列表
     */
    @Select("SELECT click_time FROM torrent_click_history WHERE uid = #{uid} AND tid = #{tid} ORDER BY click_time DESC")
    List<Date> getUserTorrentClickTimes(@Param("uid") int uid, @Param("tid") Integer tid);
} 